/*
 * Created on Sep 25, 2003
 * Local Projects Database
 * (c) 2003 eRomania Gateway Association
 *
 */
package ro.gateway.aida.usr.messaging;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;



/**
 * @author Mihai Postelnicu<p>
 *
 *  e-mail (mihai@ro-gateway.org)<br>
 * (c) 2003 by eRomania Gateway<p>
 */
public class EventDB extends DBPersistenceManager
{
      private EventDB( PersistenceToken token )
      {
            super( token );
      }

      public static EventDB getManager( PersistenceToken token )
      {
            return new EventDB( token );
      }

      public int add( Event m ) throws SQLException
      {
            Connection con = getConnection();
            int ret = 0;

            try
            {
                  PreparedStatement p = con.prepareStatement( ADD_MESSAGE );
                  p.setLong( 1, m.getSourceObjId() );
                  p.setLong( 2, m.getSourceUserId() );
                  p.setInt( 3, m.getSourceType() );
                  p.setInt( 4, m.getEventType() );
                  p.setDate( 5, new Date( System.currentTimeMillis() ) );
                  p.executeUpdate();

                  p.close();
                  p = con.prepareStatement( GET_MAX_LOG );

                  ResultSet rs = p.executeQuery();

                  if ( rs.next() )
                  {
                        ret = rs.getInt( 1 );
                  }

                  p.close();


                  return ret;
            }
            finally
            {
                  con.close();
            }
      }

      public Event getById( long id ) throws SQLException
      {
            Connection con = getConnection();
            Event r = new Event();

            try
            {
                  PreparedStatement p = con.prepareStatement( GET_MESSAGE );
                  p.setLong( 1, id );

                  ResultSet rs = p.executeQuery();

                  if ( rs.next() )
                  {
                        r.setEventId( id );
                        r.setSourceObjId( rs.getInt( 1 ) );
                        r.setSourceUserId( rs.getInt( 2 ) );
                        r.setSourceType( rs.getInt( 3 ) );
                        r.setEventType( rs.getInt( 4 ) );
                        r.setSent( rs.getDate( 5 ) );
                        r.setSourceUserName( rs.getString( 6 ) + " " + rs.getString( 7 ) );
                  }

                  rs.close();
                  p.close();


                  return r;
            }
            finally
            {
                  con.close();
            }
      }

      public Vector getAll() throws SQLException
      {
            Connection con = getConnection();
            Vector v = new Vector();

            try
            {
                  PreparedStatement p = con.prepareStatement( GET_ALL );
                  ResultSet rs = p.executeQuery();

                  while ( rs.next() )
                  {
                        Event r = new Event();
                        r.setSourceObjId( rs.getInt( 1 ) );
                        r.setSourceUserId( rs.getInt( 2 ) );
                        r.setSourceType( rs.getInt( 3 ) );
                        r.setEventType( rs.getInt( 4 ) );
                        r.setSent( rs.getDate( 5 ) );
                        r.setEventId( rs.getInt( 6 ) );
                        r.setSourceUserName( rs.getString( 7 ) + " " + rs.getString( 8 ) );
                        v.add( r );
                  }

                  rs.close();
                  p.close();


                  return v;
            }
            finally
            {
                  con.close();
            }
      }

      public Vector getByType( int type ) throws SQLException
      {
            Connection con = getConnection();
            Vector v = new Vector();

            try
            {
                  PreparedStatement p = con.prepareStatement( GET_BY_TYPE );
                  p.setInt( 1, type );

                  ResultSet rs = p.executeQuery();

                  while ( rs.next() )
                  {
                        Event r = new Event();
                        r.setSourceObjId( rs.getInt( 1 ) );
                        r.setSourceUserId( rs.getInt( 2 ) );
                        r.setSourceType( rs.getInt( 3 ) );
                        r.setEventType( rs.getInt( 4 ) );
                        r.setSent( rs.getDate( 5 ) );
                        r.setEventId( rs.getInt( 6 ) );
                        r.setSourceUserName( rs.getString( 7 ) + " " + rs.getString( 8 ) );
                        v.add( r );
                  }

                  rs.close();
                  p.close();


                  return v;
            }
            finally
            {
                  con.close();
            }
      }

      public static final String ADD_MESSAGE =
            "INSERT INTO events (source_obj_id,source_user_id,source_type,event_type,sent)" +
            " VALUES (?,?,?,?,?)";
      public static final String GET_MESSAGE = "SELECT e.source_obj_id,e.source_user_id,e.source_type," +
            "e.event_type,e.sent,u.first_names,u.last_name" +
            " FROM events e,users u WHERE e.id=? AND e.source_user_id=u.id";
      public static final String GET_ALL = "SELECT e.source_obj_id,e.source_user_id,e.source_type,e.event_type," +
            "e.sent,e.id,u.first_names,u.last_name FROM events e,users u WHERE e.source_user_id=u.id";
      public static final String GET_BY_TYPE = "SELECT e.source_obj_id,e.source_user_id,e.source_type,e.event_type," +
            "e.sent,e.id,u.first_names,u.last_name FROM events e,users u WHERE e.event_type=? AND e.source_user_id=u.id";
      public static final String GET_MAX_LOG = "SELECT MAX(id) FROM events";
}
